Alter  PROC sp_create_update_memory_sort_lot_bin (

 	@PART_NUMBER	VARCHAR(50), 
 	@LOT_NUMBER	VARCHAR(50),
	@QTY_IN      	INT,
	@QTY_OUT     	INT,
	@YIELD	     	FLOAT,
	@DATE_IN     	VARCHAR(50),
	@Date_Out	varchar(50),
 	@VENDOR_NAME 	VARCHAR(50),
	@WAFER_ID	INT,
	@BIN1_QTY	INT,
	@BIN2_QTY	INT,
	@BIN3_QTY	INT,
	@PS_TYPE_NAME	VARCHAR(50),
	@MRB_STATUS     BIT,
	@BIN1_BALANCE_QTY INT,
	@BIN2_BALANCE_QTY INT,
	@BIN3_BALANCE_QTY INT		
	
 )
 
 AS
 BEGIN
 
 	DECLARE @PROCESS_STEP_ID BIGINT 
 	DECLARE @PREVIOUS_PROCESS_STEP_ID BIGINT
 	DECLARE @LOCATION_ID INT
 	DECLARE @TEMP_LOT_ID BIGINT
	DECLARE @MAX_TIMESTAMP DATETIME
	DECLARE @LOT_CURRENT_STATUS VARCHAR(20)
	DECLARE @TEMP_QTY_OUT INT
	DECLARE @TEMP_QTY_IN  INT
	DECLARE @TEMP_DATE_IN DATETIME
	DECLARE @TEMP_DATE_OUT DATETIME
	declare @LOT_ID BIGINT

	IF @QTY_OUT = ''
	BEGIN
		RETURN
	END

	IF @PS_TYPE_NAME = 'SORT'
	BEGIN
	
	
		SELECT @PROCESS_STEP_ID = PROCESS_STEP.PROCESSSTEP_ID FROM PRODUCT,PROCESS_STEP WHERE PRODUCT.PART_NUMBER = @PART_NUMBER AND PS_TYPE_NAME LIKE @PS_TYPE_NAME AND PRODUCT.PROCESSSTEP_ID=PROCESS_STEP.PROCESSSTEP_ID 
  		SELECT @LOCATION_ID = LOCATION_ID FROM LOCATION WHERE L_TYPE = @PS_TYPE_NAME AND L_VENDOR_NAME = @VENDOR_NAME

		IF @LOCATION_ID = '' OR @LOCATION_ID IS NULL
		BEGIN
			-- dummy update to avoid SQLException
			UPDATE LOT_TABLE SET LOT_NUMBER = 'LOT_NUMBER' WHERE LOT_NUMBER = 'LOT_NUMBER'
			RETURN
		END
  
		IF @PROCESS_STEP_ID IS NULL -- new Product or Product Name mismatch
  		BEGIN
  		SELECT @PROCESS_STEP_ID = PROCESS_STEP.PROCESSSTEP_ID FROM PRODUCT_MAP_TABLE,PROCESS_STEP WHERE PRODUCT_MAP_TABLE.PART_NUMBER = @PART_NUMBER AND PS_TYPE_NAME LIKE @PS_TYPE_NAME AND PRODUCT_MAP_TABLE.PROCESSSTEP_ID=PROCESS_STEP.PROCESSSTEP_ID AND PRODUCT_MAP_TABLE.LOCATION_ID = @LOCATION_ID
			IF @PROCESS_STEP_ID IS NULL
			BEGIN
				-- dummy update to avoid SQLException
				UPDATE LOT_TABLE SET LOT_NUMBER = 'LOT_NUMBER' WHERE LOT_NUMBER = 'LOT_NUMBER'
				RETURN
			END
			
		END
	 	
	 	SELECT @TEMP_LOT_ID = LOT_ID FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESS_STEP_ID AND LOCATION_ID = @LOCATION_ID AND LOT_NUMBER = @LOT_NUMBER
	 	
		IF @TEMP_LOT_ID IS NOT NULL
	 	BEGIN
			UPDATE LOT_TABLE SET LOT_CURRENT_STATUS = 'Close', LOT_ENTERED_BY = 1  WHERE LOT_ID = @TEMP_LOT_ID
			SELECT @MAX_TIMESTAMP = MAX(SORT_LOT_HISTORY.Timestamp) FROM SORT_LOT_HISTORY WHERE Lot_ID = @TEMP_LOT_ID 
			
			IF @MAX_TIMESTAMP IS NOT NULL
			BEGIN
				SELECT @LOT_CURRENT_STATUS = SL_CURRENT_STATUS, @TEMP_QTY_IN = SL_DIE_WAFER, @TEMP_QTY_OUT = SL_DIE_QTY_OUT  FROM SORT_LOT_HISTORY WHERE LOT_ID = @TEMP_LOT_ID AND SORT_LOT_HISTORY.TIMESTAMP = @MAX_TIMESTAMP AND SL_DIE_WAFER = @QTY_IN
				IF @LOT_CURRENT_STATUS = 'Close'
				BEGIN
					IF @TEMP_QTY_OUT = @QTY_OUT
					BEGIN
						UPDATE SORT_LOT_HISTORY SET  SL_DATE_IN=@DATE_IN, SL_Date_Out = @DATE_OUT, SL_DIE_WAFER=@TEMP_QTY_IN, SL_DIE_QTY_OUT=@QTY_OUT WHERE LOT_ID = @TEMP_LOT_ID AND SORT_LOT_HISTORY.TIMESTAMP = Current_timeStamp AND SL_CURRENT_STATUS = 'Close'
					END
					ELSE
					BEGIN
						UPDATE LOT_TABLE SET LOT_NUMBER = 'LOT NUMBER' WHERE LOT_NUMBER = 'LOT NUMBER'
					END
				END
				ELSE 
				BEGIN
					UPDATE LOT_TABLE SET LOT_NUMBER = 'LOT NUMBER' WHERE LOT_NUMBER = 'LOT NUMBER'
				END
			END
			ELSE
			BEGIN
				INSERT INTO SORT_LOT_HISTORY ( LOT_ID, SL_DIE_WAFER, SL_DIE_QTY_OUT, SL_YIELD, SL_DATE_IN, SL_DATE_OUT, TIMESTAMP, SL_CURRENT_STATUS ) VALUES ( @TEMP_LOT_ID, @QTY_IN, @QTY_OUT, @YIELD, @DATE_IN, @Date_Out, CURRENT_TIMESTAMP,'Close')
			END
			
			SET @MAX_TIMESTAMP = NULL
	
	 		SET @LOT_ID = @TEMP_LOT_ID
	 	END
	 
	 	IF @PROCESS_STEP_ID IS NOT NULL AND @TEMP_LOT_ID IS NULL 
	 	BEGIN
	 		INSERT INTO LOT_TABLE ( PROCESSSTEP_ID, LOCATION_ID, LOT_NUMBER, LOT_CURRENT_STATUS,LOT_ENTERED_BY, TIMESTAMP) VALUES (@PROCESS_STEP_ID, @LOCATION_ID, @LOT_NUMBER, 'Close' ,1, CURRENT_TIMESTAMP)
	 		SET @LOT_ID = @@IDENTITY
			INSERT INTO SORT_LOT_HISTORY( LOT_ID, SL_DIE_WAFER, SL_DIE_QTY_OUT, SL_YIELD, SL_DATE_IN, SL_DATE_OUT, TIMESTAMP, SL_CURRENT_STATUS ) VALUES ( @LOT_ID, @QTY_IN, @QTY_OUT, @YIELD, @DATE_IN, @DATE_OUT, CURRENT_TIMESTAMP,'Close')
	 	END
		

	END
	IF @PS_TYPE_NAME = 'Die Bank'
	BEGIN
		SELECT @LOCATION_ID = LOCATION_ID FROM LOCATION WHERE L_TYPE = 'Die Bank' AND L_VENDOR_NAME = @VENDOR_NAME
		SELECT @PROCESS_STEP_ID = PROCESS_STEP.PROCESSSTEP_ID FROM PRODUCT,PROCESS_STEP WHERE PRODUCT.PART_NUMBER = @PART_NUMBER AND PS_TYPE_NAME LIKE 'Die Bank' AND PRODUCT.PROCESSSTEP_ID=PROCESS_STEP.PROCESSSTEP_ID 
		
		IF @LOCATION_ID = '' OR @LOCATION_ID IS NULL
		BEGIN
			-- dummy update to avoid SQLException
			UPDATE LOT_TABLE SET LOT_NUMBER = 'LOT_NUMBER' WHERE LOT_NUMBER = 'LOT_NUMBER'
			RETURN
		END
		
		IF @PROCESS_STEP_ID IS NULL
		BEGIN
			SELECT @PROCESS_STEP_ID = PROCESS_STEP.PROCESSSTEP_ID FROM PRODUCT_MAP_TABLE,PROCESS_STEP WHERE PRODUCT_MAP_TABLE.PART_NUMBER = @PART_NUMBER  AND PS_TYPE_NAME='Die Bank' AND PRODUCT_MAP_TABLE.PROCESSSTEP_ID=PROCESS_STEP.PROCESSSTEP_ID AND PRODUCT_MAP_TABLE.LOCATION_ID = @LOCATION_ID
			IF @PROCESS_STEP_ID IS NULL
			BEGIN
				UPDATE LOT_TABLE SET LOT_NUMBER = 'LOT_NUMBER' WHERE LOT_NUMBER = 'LOT_NUMBER'
				RETURN
			END
			
		END
	 	
	 	SELECT @TEMP_LOT_ID = LOT_ID FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESS_STEP_ID AND LOCATION_ID = @LOCATION_ID AND LOT_NUMBER = @LOT_NUMBER
	 	
		IF @TEMP_LOT_ID IS NOT NULL
	 	BEGIN
			UPDATE LOT_TABLE SET LOT_CURRENT_STATUS = 'Close', LOT_ENTERED_BY = 1  WHERE LOT_ID = @TEMP_LOT_ID
			SELECT @MAX_TIMESTAMP = MAX(INVENTORY_LOT_HISTORY.Timestamp) FROM INVENTORY_LOT_HISTORY WHERE Lot_ID = @TEMP_LOT_ID 
			
			IF @MAX_TIMESTAMP IS NOT NULL
			BEGIN
				SELECT @LOT_CURRENT_STATUS = IL_CURRENT_STATUS, @TEMP_QTY_IN = IL_QTY_IN, @TEMP_QTY_OUT = IL_QTY_OUT  FROM INVENTORY_LOT_HISTORY WHERE LOT_ID = @TEMP_LOT_ID AND INVENTORY_LOT_HISTORY.TIMESTAMP = @MAX_TIMESTAMP AND IL_Qty_In = @QTY_IN
				IF @LOT_CURRENT_STATUS = 'Close'
				BEGIN
					IF @TEMP_QTY_OUT = @QTY_OUT
					BEGIN
						UPDATE INVENTORY_LOT_HISTORY SET  IL_DATE_IN=@DATE_IN, IL_DATE_OUT = @DATE_OUT, IL_Qty_In=@TEMP_QTY_IN, IL_QTY_OUT=@QTY_OUT WHERE LOT_ID = @TEMP_LOT_ID AND INVENTORY_LOT_HISTORY.TIMESTAMP = CURRENT_TIMESTAMP AND IL_CURRENT_STATUS = 'Close'
					END
					ELSE
					BEGIN
						UPDATE LOT_TABLE SET LOT_NUMBER = 'LOT NUMBER' WHERE LOT_NUMBER = 'LOT NUMBER'
					END
				END
				ELSE 
				BEGIN
					UPDATE LOT_TABLE SET LOT_NUMBER = 'LOT NUMBER' WHERE LOT_NUMBER = 'LOT NUMBER'
				END
			END
			ELSE
			BEGIN
				INSERT INTO INVENTORY_LOT_HISTORY ( LOT_ID, Il_Qty_In, IL_QTY_OUT, IL_DATE_IN, IL_DATE_OUT, TIMESTAMP, IL_CURRENT_STATUS ) VALUES ( @TEMP_LOT_ID, @QTY_IN, @QTY_OUT, @DATE_IN, @DATE_OUT, CURRENT_TIMESTAMP,'Close')
			END
			
			SET @MAX_TIMESTAMP = NULL
	
	 		SET @LOT_ID = @TEMP_LOT_ID
	 		
	 	END
	 
	 	IF @PROCESS_STEP_ID IS NOT NULL AND @TEMP_LOT_ID IS NULL 
	 	BEGIN
	 		INSERT INTO LOT_TABLE ( PROCESSSTEP_ID, LOCATION_ID, LOT_NUMBER, LOT_CURRENT_STATUS,LOT_ENTERED_BY, TIMESTAMP) VALUES (@PROCESS_STEP_ID, @LOCATION_ID, @LOT_NUMBER, 'Close' ,1, CURRENT_TIMESTAMP)
	 		SET @LOT_ID = @@IDENTITY
			INSERT INTO INVENTORY_LOT_HISTORY( LOT_ID, Il_Qty_In, IL_QTY_OUT, IL_DATE_IN, IL_DATE_OUT, TIMESTAMP, IL_CURRENT_STATUS ) VALUES ( @LOT_ID, @QTY_IN,@QTY_OUT, @DATE_IN, @DATE_OUT, CURRENT_TIMESTAMP,'Close')
	 	END
		
	END
	
	IF @LOT_ID > 0 and @QTY_OUT > 0 and @WAFER_ID > 0
	BEGIN
		
		IF @BIN2_QTY = '' OR @BIN2_QTY IS NULL
		BEGIN
			SET @BIN2_QTY = 0		
		END
		IF @BIN3_QTY = '' OR @BIN3_QTY IS NULL
		BEGIN
			SET @BIN3_QTY = 0		
		END

		DELETE FROM INFORMATIVE_BINNING WHERE LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID
		INSERT INTO INFORMATIVE_BINNING ( LOT_ID, WAFER_ID, GOOD_DICE, BIN_1, BIN_2, BIN_3, MRB_LIMIT ) VALUES( @LOT_ID, @WAFER_ID, @QTY_OUT, @Bin1_Qty, @Bin2_Qty, @BIN3_QTY, @MRB_Status)
		
		IF @MRB_STATUS > 0 AND @PS_TYPE_NAME = 'Sort'
		BEGIN
			DELETE FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID
			INSERT INTO SORT_BIN_RELEASE_TABLE( LOT_ID, WAFER_ID, BIN_1, BIN_2, BIN_3, BIN_4, BIN_5, BIN_6, BIN_7, BIN_8, BIN_9, BIN_10, BIN_11, BIN_12, BIN_13, BIN_14, BIN_15, Bin_1_Balance_Qty, Bin_2_Balance_Qty, Bin_3_Balance_Qty, Bin_4_Balance_Qty, Bin_5_Balance_Qty, Bin_6_Balance_Qty, Bin_7_Balance_Qty, Bin_8_Balance_Qty, Bin_9_Balance_Qty, Bin_10_Balance_Qty, Bin_11_Balance_Qty, Bin_12_Balance_Qty, Bin_13_Balance_Qty, Bin_14_Balance_Qty, Bin_15_Balance_Qty ) VALUES ( @LOT_ID, @WAFER_ID, @BIN1_QTY, @BIN2_QTY, @BIN3_QTY, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, @bin1_balance_qty, @bin2_balance_qty, @bin3_balance_qty, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 )
		END

		set @PART_NUMBER	= null 
		set @LOT_NUMBER		= null 
		set @QTY_IN   		= null 
		set @QTY_OUT  		= null 
		set @YIELD		= null 
		set @DATE_IN     	= null 
		set @VENDOR_NAME 	= null 
		set @Wafer_Id		= null
		set @Bin1_Qty		= null
		set @Bin2_Qty 		= null
	END	
 
 END
 